#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   sku2_inverse_rate_calculation.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2021-06-30 14:40   pengwei.sun      1.0         None
'''
import os
import sys
sys.path.append(os.getcwd())
from src.utils.db_processor import mysql_prediction_processor
import pandas as pd
from src.utils.config import logger
from src.mobile.android_levelrate.reverse.function_utils import save_pickle_data,load_pickle_data,combine_rank_min_max_price,\
    inverse_rate_fun,check_conflict_file
import pickle
import numpy as np
from src.mobile.android_levelrate.reverse.sku2_common_variable import  FILE_DIR,COLUMNS_RANK_LIST,COLUMNS_RANK_COLUMN_LIST,QUERY_SKU_COLUMN_RANK_SQL
import concurrent.futures
from multiprocessing import Pool
from functools import partial
import time
from itertools import repeat


#where  a.product_id not in (32291,36046)
column_list=['product_brand_name','product_key','product_name','product_sku_key','product_level_key','template_rank','product_sku_name','sku_concat_name','process_price','saleprice',
             'product_level_name','column_name','rank_column','rank_price_max','rank_price_min','iter_size','iter_size_bi','iverse','iverse_bi','weight_cnt','iverse_column',
             'thisprice','price_0_7', 'sale_num_0_7','price_0_3', 'sale_num_0_3','process_price_inverse_begin','forecast_reference_price','level_rate_price','reverse_cnt']

#原始函数无法接受一个数组，进行数组中转使用
def myproperty_inverse_rate_fun(kwgs):
    res = property_inverse_rate_fun(*kwgs)
    return res

def property_inverse_rate_fun(group,column_name,column_lists):
    resDf = pd.DataFrame(columns=column_lists)
    group = group[1]
    group = group.drop_duplicates()
    group['weight_cnt'] = 0
    level_list = list(set(group['product_level_name'])) #判断等级列表，根据等级进行倒挂处理
    for level in level_list:
        group_level = group.loc[group.product_level_name == level]
        group_level = group_level.sort_values('rank_column', ascending=True)
        group_level.reset_index(drop=True, inplace=True)

        size = group_level.shape[0]
        rank_size = len(set(group_level['rank_column']))
        result_t = group_level
        if rank_size < 2:
            resDf = resDf.append(result_t.copy())
            continue


        for index in range(size):

            iter_size = 0
            iter_size_bi = 0
            iverse = 0
            iverse_bi = 0
            if index + 1 <= size and result_t.loc[index, 'product_level_name']:
                for i in range(index + 1, size, 1):
                    if result_t.loc[index, 'rank_column'] == result_t.loc[i, 'rank_column']:
                        continue
                    iter_size += 1
                    iter_size_bi += 1
                    if result_t.loc[index, 'process_price'] < result_t.loc[i, 'process_price'] and result_t.loc[
                        index, 'rank_column'] < result_t.loc[i, 'rank_column']:
                        iverse_bi = iverse_bi + 1
                        result_t.loc[i, 'weight_cnt'] = result_t.loc[i, 'weight_cnt'] + 1
                        result_t.loc[index, 'weight_cnt'] = result_t.loc[index, 'weight_cnt'] + 1

                    if result_t.loc[index, 'saleprice'] < result_t.loc[i, 'saleprice'] and result_t.loc[
                        index, 'rank_column'] < result_t.loc[i, 'rank_column']:
                        iverse = iverse + 1

                result_t.loc[index, 'iter_size'] = iter_size
                result_t.loc[index, 'iter_size_bi'] = iter_size_bi
                result_t.loc[index, 'iverse'] = iverse
                result_t.loc[index, 'iverse_bi'] = iverse_bi
        result_t[column_name] = result_t['column_name']
        resDf = resDf.append(result_t.copy())
    return resDf

def property_sku_concat_name(result,rank_column,column_name):
    t1 = time.time()
    result_t = result.copy()
    column_lists = column_list.copy()
    column_lists.append(rank_column)
    column_lists.append(column_name)
    result_t = result_t.loc[result_t.level_sub.isin(['S', 'A', 'B', 'C'])]
    result_t = result_t.loc[result_t[rank_column] > 0]
    result_t['column_name'] = result_t[column_name]
    result_t[column_name] = ''
    result_t['iverse_column'] = column_name
    result_t['rank_column'] = result_t[rank_column]

    # product_name | | ' ' | | warranty_duration_name | | ' ' | | color_name | | ' ' | | network_standard_name | | ' ' | | memory_capacity_name | | ' ' | | memory_name | | ' ' | | small_model_name | | ' ' | | purchase_channel_name as concat_name
    result_t['weight_cnt'] = 0
    # if flag == True:
    result_t['sku_concat_name'] = result_t['product_name'] + ' ' + result_t['warranty_duration_name'] + ' ' + \
                                  result_t['color_name'] + ' ' \
                                  + result_t['network_standard_name'] + ' ' + result_t['memory_capacity_name'] + ' ' \
                                  + result_t['memory_name'] + ' ' + result_t['small_model_name'] + ' ' \
                                  + result_t['purchase_channel_name']
    return result_t

def property_inverse_rate_process(result,rank_column,column_name,flag=True):

    t1 = time.time()
    result_t=result.copy()
    column_lists=column_list.copy()
    column_lists.append(rank_column)
    column_lists.append(column_name)
    resDf = pd.DataFrame(columns=column_lists)
    # resDf = pd.DataFrame(columns=result_t.columns.tolist())
    columns=column_list+[column_name,rank_column,'level_sub']
    process_df=result_t[columns]
    # process_df = result_t
    grouped=process_df.groupby(by=['product_key','product_name','sku_concat_name','product_level_name'])
    # for name, group in grouped:
    # process_df['product_sku_key'] =process_df['product_sku_key'].astype(int)
    # return resDf
    total_gp = grouped.ngroups
    iter=0
    if total_gp>5:
        with Pool(8) as executor:
            for res in executor.map(myproperty_inverse_rate_fun,
                                                   zip(grouped,repeat(column_name),repeat(column_lists))):
                resDf = resDf.append(res.copy())
                iter += 1
                logger.info('total_gp = {} ,iter={} report== is prime:'.format(total_gp, iter))
        logger.info(' inverse_rate_process end use time @{}'.format((time.time() - t1) / 60))
    else:
        for group in grouped:
            res = property_inverse_rate_fun(group,column_name,column_lists)
            resDf = resDf.append(res.copy())
    resDf = resDf.drop_duplicates()
    rt_result = resDf.copy()
    return resDf

"""
属性倒挂处理逻辑
"""
# def property_inverse_process_fun(input_df,rt_result,rank_column,column_name):
#
#     # input_df['process_price_inverse_begin']=input_df['process_price']
#     result_t=input_df.sort_values(by=['rank_column', 'process_price'],ascending=[True, False])
#     # result_t['reverse_cnt']=0
#     resDf = pd.DataFrame(columns=result_t.columns.tolist())
#     grouped = result_t.groupby(by=['sku_concat_name','product_level_name'])
#     iter = 0
#     totalCnt = grouped.count().shape[0]
#     for name, group in grouped:
#         group = group.sort_values(by=['rank_column', 'process_price'],ascending=[True, False])
#         group.reset_index(drop=True, inplace=True)
#         size = group.shape[0]
#         if size<2:
#             continue
#         result_t = group
#         logger.info('product_key={},sku_concat_name={};product_level_name={}'.format(result_t.loc[0,'product_key'],result_t.loc[0,'sku_concat_name'],result_t.loc[0,'product_level_name']))
#         # if result_t.loc[result_t.product_sku_key.isin([772111])].shape[0]>0 and result_t.loc[result_t.product_level_name.isin(['A+','A1','A'])].shape[0]>0:
#         #     logger.info('dsds')
#
#         for index in range(1,size*2,1):
#
#             result_t = group
#             result_t['weight_cnt']=0
#             result_t=property_inverse_rate_process(result_t,rank_column,column_name,False) #属性倒挂处理之前，先标识出来属性是否倒挂，判断是否需要处理
#             #如果没发生倒挂，则直接返回 不需要处理
#             result_t=result_t.loc[result_t.weight_cnt>0]
#             reverse_size = result_t.shape[0]
#             if reverse_size==0:
#                 break
#             if result_t.loc[result_t.product_sku_key.isin([772111])].shape[0] > 0 and \
#                     result_t.loc[result_t.product_level_name.isin(['A+', 'A1', 'A','A2'])].shape[0] > 0:
#                 logger.info('dsds')
#             #标识每个等级价格的上下限
#             targer_sku_df=rt_result.loc[rt_result.product_sku_key.isin(result_t['product_sku_key'].tolist())]
#             result_t=combine_rank_min_max_price(result_t,targer_sku_df)
#             result_t.reset_index(drop=True, inplace=True)
#
#             tmp1=result_t.loc[result_t.sale_num_0_7 < 10] #倒挂处理，排除售卖数量大于10的，开始处理
#             max_index=tmp1.loc[result_t.weight_cnt==tmp1.weight_cnt.max()]
#             max_index.sort_values(by=['reverse_cnt','sale_num_0_7'], ascending=[False,True],inplace=True)
#
#             #遇到排除>=10的售卖量后，无数据可用
#             if max_index.shape[0]==0:
#                 max_index = result_t.loc[result_t.weight_cnt == result_t.weight_cnt.max()]
#                 max_index.sort_values(by=['reverse_cnt','sale_num_0_7'], ascending=[False,True],inplace=True)
#
#             reverse_index = max_index.index[0]
#             logger.info('iter={}'.format(index))
#             if result_t.loc[reverse_index,'sale_num_0_7']>=10:
#                 logger.info('产生num10的数据')
#
#             if reverse_index+1<reverse_size:
#                 middle_price=result_t.loc[reverse_index, 'process_price']
#                 ceil_price=result_t.loc[reverse_index, 'rank_price_min']
#                 curr_floor_price=result_t.loc[reverse_index, 'rank_price_max']
#                 # current_price = result_t.loc[reverse_index, 'process_price']
#                 next_price = result_t.loc[reverse_index + 1, 'process_price']
#
#                 floor_price = result_t.loc[reverse_index + 1, 'rank_price_max']
#                 if reverse_index>0:
#                     up_price = result_t.loc[reverse_index - 1, 'process_price']
#                     if result_t.loc[reverse_index-1, 'sale_num_0_7'] >= 0 and result_t.loc[reverse_index-1, 'sale_num_0_7'] >= result_t.loc[reverse_index-1, 'sale_num_0_7']:
#                         max_next_price=max(up_price*0.99,curr_floor_price+1)
#                         # group.loc[reverse_index,'process_price']=media_price
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (group.product_level_key == result_t.loc[reverse_index, 'product_level_key']), 'process_price']=max_next_price
#
#                         rt_result.loc[(rt_result.product_sku_key==result_t.loc[reverse_index,'product_sku_key']) & (rt_result.product_level_key==result_t.loc[reverse_index,'product_level_key']),'process_price']=max_next_price
#                         rt_result.loc[(rt_result.product_sku_key==result_t.loc[reverse_index,'product_sku_key']) & (rt_result.product_level_key==result_t.loc[reverse_index,'product_level_key']),'reverse']=1
#                         continue
#                 # if reverse_index == 0:
#                 if next_price<ceil_price:
#                     if next_price>middle_price:
#
#                         max_next_price=min(next_price*1.01,ceil_price-1)
#                         # group.loc[reverse_index,'process_price']=media_price
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (group.product_level_key == result_t.loc[reverse_index, 'product_level_key']), 'process_price']=max_next_price
#
#                         rt_result.loc[(rt_result.product_sku_key==result_t.loc[reverse_index,'product_sku_key']) & (rt_result.product_level_key==result_t.loc[reverse_index,'product_level_key']),'process_price']=max_next_price
#                         rt_result.loc[(rt_result.product_sku_key==result_t.loc[reverse_index,'product_sku_key']) & (rt_result.product_level_key==result_t.loc[reverse_index,'product_level_key']),'reverse']=1
#                         continue
#                     else:
#                         logger.info('异常情况={}'.format(result_t))
#                 elif floor_price<middle_price:
#                     max_next_price=max(middle_price*0.99,(floor_price+middle_price)/2)
#                     # group.loc[reverse_index+1,'process_price']=max_next_price
#                     group.loc[(group.product_sku_key == result_t.loc[reverse_index+1, 'product_sku_key']) & (group.product_level_key == result_t.loc[reverse_index+1, 'product_level_key']), 'process_price'] = max_next_price
#
#                     rt_result.loc[(rt_result.product_sku_key==result_t.loc[reverse_index+1,'product_sku_key']) & (rt_result.product_level_key==result_t.loc[reverse_index+1,'product_level_key']),'process_price']=max_next_price
#                     rt_result.loc[(rt_result.product_sku_key==result_t.loc[reverse_index+1,'product_sku_key']) & (rt_result.product_level_key==result_t.loc[reverse_index+1,'product_level_key']),'reverse']=1
#                     continue
#
#                 else:
#                     if floor_price < ceil_price:
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index+1, 'product_sku_key']) & (
#                                 group.product_level_key == result_t.loc[
#                             reverse_index+1, 'product_level_key']), 'process_price'] = min(
#                             (middle_price + floor_price) / 2,
#                             min((middle_price + ceil_price) / 2, ceil_price * 0.99) * 0.99)
#
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index , 'product_sku_key']) & (
#                                 group.product_level_key == result_t.loc[
#                             reverse_index , 'product_level_key']), 'process_price'] = min(
#                             (middle_price + ceil_price) / 2, ceil_price * 0.99)
#
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index+1, 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index+1, 'product_level_key']), 'process_price'] = min(
#                             (middle_price + floor_price) / 2,
#                             min((middle_price + ceil_price) / 2, ceil_price * 0.99) * 0.99)
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index , 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index , 'product_level_key']), 'process_price'] = min(
#                             (middle_price + ceil_price) / 2, ceil_price * 0.99)
#
#
#                     elif reverse_index + 2<reverse_size:
#                         result_t.loc[reverse_index + 1, 'process_price']=max(result_t.loc[reverse_index, 'rank_price_max'],result_t.loc[reverse_index + 2, 'process_price']+2)
#
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
#                                 group.product_level_key == result_t.loc[
#                             reverse_index + 1, 'product_level_key']), 'process_price'] =max(result_t.loc[reverse_index, 'rank_price_max'],result_t.loc[reverse_index + 2, 'process_price']+2)
#
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index + 1, 'product_level_key']), 'process_price'] =max(result_t.loc[reverse_index, 'rank_price_max'],result_t.loc[reverse_index + 2, 'process_price']+2)
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index + 1, 'product_level_key']), 'reverse'] = 1
#                         print('dsd')
#                     else:
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                 group.product_level_key == result_t.loc[
#                             reverse_index, 'product_level_key']), 'process_price'] =ceil_price * 0.99
#
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index, 'product_level_key']), 'process_price'] =  ceil_price * 0.99
#
#                         print('未捕获到价格')
#
#
#                     continue
#                 logger.info('end!')
#
#
#             else: #位于处理数据的最后一行
#                 ceil_price = result_t.loc[reverse_index - 1, 'process_price']
#                 ceil_sale_num_0_7 = result_t.loc[reverse_index - 1, 'sale_num_0_7']
#                 middls_sale_num_0_7 = result_t.loc[reverse_index, 'sale_num_0_7']
#                 ceil_floor_price = result_t.loc[reverse_index - 1, 'rank_price_max']
#                 ceil_ceil_price = result_t.loc[reverse_index - 1, 'rank_price_min']
#                 middle_price = result_t.loc[reverse_index, 'process_price']
#                 floor_price = result_t.loc[reverse_index, 'rank_price_max']
#                 if floor_price < ceil_ceil_price:
#                     # result_t.loc[reverse_index, 'process_price']=(ceil_price+floor_price)/2
#                     if floor_price<ceil_price:
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                 group.product_level_key == result_t.loc[
#                             reverse_index, 'product_level_key']), 'process_price'] = max((middle_price + floor_price) / 2,ceil_price * 0.99)
#
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index, 'product_level_key']), 'process_price'] = max((middle_price + floor_price) / 2,ceil_price * 0.99)
#
#                     else:#针对下面价格比上面最高价格还高的情况 需要 把上面往最高调，把下面往最低调
#                         if ceil_sale_num_0_7>0 and ceil_sale_num_0_7>middls_sale_num_0_7: #针对上面有出货数量，且比较多的情况，保持上面价格不动 压低下面价格，即使保持下面整个sku价格统一下压也可接受
#                             group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                     group.product_level_key == result_t.loc[
#                                 reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
#
#                             rt_result.loc[
#                                 (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                         rt_result.product_level_key == result_t.loc[
#                                     reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
#                             continue
#                         else:
#                             group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                     group.product_level_key == result_t.loc[
#                                 reverse_index, 'product_level_key']), 'process_price'] = min((middle_price + floor_price) / 2,min((middle_price + ceil_ceil_price) / 2,ceil_ceil_price*0.99)*0.99)
#
#                             group.loc[(group.product_sku_key == result_t.loc[reverse_index-1, 'product_sku_key']) & (
#                                     group.product_level_key == result_t.loc[
#                                 reverse_index-1, 'product_level_key']), 'process_price'] = min((middle_price + ceil_ceil_price) / 2,ceil_ceil_price*0.99)
#
#                             rt_result.loc[
#                                 (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                         rt_result.product_level_key == result_t.loc[
#                                     reverse_index, 'product_level_key']), 'process_price'] = min((middle_price + floor_price) / 2,min((middle_price + ceil_ceil_price) / 2,ceil_ceil_price*0.99)*0.99)
#                             rt_result.loc[
#                                 (rt_result.product_sku_key == result_t.loc[reverse_index-1, 'product_sku_key']) & (
#                                         rt_result.product_level_key == result_t.loc[
#                                     reverse_index-1, 'product_level_key']), 'process_price'] =  min((middle_price + ceil_ceil_price) / 2,ceil_ceil_price*0.99)
#
#                 else:
#                     # result_t.loc[reverse_index, 'process_price'] = max(floor_price*1.01,ceil_price*0.99)
#                     if pd.isnull(floor_price) :
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                 group.product_level_key == result_t.loc[
#                             reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
#
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
#                         continue
#
#                     if ceil_sale_num_0_7 > 0 and ceil_sale_num_0_7 > middls_sale_num_0_7:  # 针对上面有出货数量，且比较多的情况，保持上面价格不动 压低下面价格，即使保持下面整个sku价格统一下压也可接受
#                         group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                 group.product_level_key == result_t.loc[
#                             reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
#
#                         rt_result.loc[
#                             (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                     rt_result.product_level_key == result_t.loc[
#                                 reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
#                         continue
#
#                     group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                             group.product_level_key == result_t.loc[
#                         reverse_index, 'product_level_key']), 'process_price'] =  max(floor_price*1.01,ceil_price*0.99)
#
#                     rt_result.loc[
#                         (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                                 rt_result.product_level_key == result_t.loc[
#                             reverse_index, 'product_level_key']), 'process_price'] =  max(floor_price*1.01,ceil_price*0.99)
#                     print('ds')
#                 rt_result.loc[
#                     (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
#                             rt_result.product_level_key == result_t.loc[
#                         reverse_index, 'product_level_key']), 'reverse'] += 1
#
#
#             result_t.loc[reverse_index, 'reverse_cnt']+=1
#             # logger.info('iter={}'.format(index))
#
#         resDf = resDf.append(group.copy())
#         iter += 1
#         logger.info('totalCnt= {},iter = {}'.format(totalCnt, iter))
#     return resDf,rt_result

def property_inverse_process_multi(input_df,rt_result,rank_column,column_name):
    t1 = time.time()
    column_lists = column_list.copy()
    column_lists.append(rank_column)
    column_lists.append(column_name)
    resDf = pd.DataFrame(columns=column_lists)
    grouped=input_df.groupby(by='product_key')
    # for name, group in grouped:
    # process_df['product_sku_key'] =process_df['product_sku_key'].astype(int)
    # return resDf
    total_gp = grouped.ngroups
    iter=0
    para = partial(property_inverse_process_fun,rt_result,rank_column,column_name)
    if total_gp>1:
        with Pool(8) as executor:
            for res in executor.map(para,grouped):
                resDf = resDf.append(res.copy())
                iter += 1
                logger.info('total_gp = {} ,iter={} report== is prime:'.format(total_gp, iter))
        logger.info(' inverse_rate_process end use time @{}'.format((time.time() - t1) / 60))
    else:
        for group in grouped:
            res = para(group)
            resDf = resDf.append(res.copy())
    resDf = resDf.drop_duplicates()

    return resDf

def property_inverse_process_fun(origin_df,rank_column,column_name,group):
    # input_df['process_price_inverse_begin']=input_df['process_price']
    product_key = group[0]
    input_df=group[1]
    # product_key = input_df.loc[0,'product_key']
    rt_result=origin_df.loc[origin_df.product_key.isin([product_key])]

    result_t=input_df.sort_values(by=['rank_column', 'process_price'],ascending=[True, False])
    # result_t['reverse_cnt']=0
    resDf = pd.DataFrame(columns=result_t.columns.tolist())
    grouped = result_t.groupby(by=['sku_concat_name','product_level_name'])
    iter = 0
    totalCnt = grouped.count().shape[0]
    for name, group in grouped:
        group = group.sort_values(by=['rank_column', 'process_price'],ascending=[True, False])
        group.reset_index(drop=True, inplace=True)
        size = group.shape[0]
        if size<2:
            continue
        if group[rank_column].drop_duplicates().shape[0]<2:
            continue
        result_t = group
        logger.info('rank_column={},sku_concat_name={};product_level_name={}'.format(rank_column,result_t.loc[0,'sku_concat_name'],result_t.loc[0,'product_level_name']))
        if result_t.loc[
            (result_t.product_level_key == 408) & (result_t.memory_id == 4068) & (result_t.networks_id == 3084)].shape[0]>1:
            logger.info('dssd')
        for index in range(1,size*2,1):
            result_t = group
            result_t['weight_cnt']=0
            result_t=property_inverse_rate_process(result_t,rank_column,column_name,False)
            targer_sku_df=rt_result.loc[rt_result.product_sku_key.isin(result_t['product_sku_key'].tolist())]
            result_t=combine_rank_min_max_price(result_t,targer_sku_df)
            result_t=result_t.loc[result_t.weight_cnt>0]
            result_t.reset_index(drop=True, inplace=True)

            tmp1=result_t.loc[result_t.sale_num_0_7 < 10]
            # max_index=tmp1.loc[result_t.weight_cnt==tmp1.weight_cnt.max()]
            max_index = tmp1.loc[result_t.sale_num_0_7 == tmp1.sale_num_0_7.min()]
            max_index.sort_values(by=['reverse_cnt', 'sale_num_0_7'], ascending=[False, True], inplace=True)

            reverse_size = result_t.shape[0]
            if reverse_size==0:
                break
            #遇到排除>=10的售卖量后，无数据可用
            if max_index.shape[0]==0:
                max_index = result_t.loc[result_t.weight_cnt == result_t.weight_cnt.max()]
                max_index.sort_values(by=['reverse_cnt','sale_num_0_7'], ascending=[False,True],inplace=True)

            reverse_index = max_index.index[0]
            if result_t.loc[reverse_index,'sale_num_0_7']>=10:
                logger.info('cal2产生num10的数据')
            logger.info('iter={}'.format(index))
            if reverse_index+1<reverse_size:
                middle_price=result_t.loc[reverse_index, 'process_price']
                ceil_price=result_t.loc[reverse_index, 'rank_price_min']
                curr_floor_price = result_t.loc[reverse_index, 'rank_price_max']
                current_price = result_t.loc[reverse_index, 'process_price']
                next_price = result_t.loc[reverse_index + 1, 'process_price']
                floor_price = result_t.loc[reverse_index + 1, 'rank_price_max']

                if reverse_index > 0:
                    up_price = result_t.loc[reverse_index - 1, 'process_price']
                    if result_t.loc[reverse_index - 1, 'sale_num_0_7'] >= 0 and result_t.loc[
                        reverse_index - 1, 'sale_num_0_7'] >= result_t.loc[reverse_index , 'sale_num_0_7']:
                        max_next_price = max(up_price * 0.99, curr_floor_price + 1)
                        # group.loc[reverse_index,'process_price']=media_price
                        group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    group.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = max_next_price

                        rt_result.loc[(rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = max_next_price
                        rt_result.loc[(rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'reverse'] = 1
                        continue
                if next_price < ceil_price:
                    if next_price > middle_price:

                        max_next_price = min(next_price * 1.01, ceil_price - 1)
                        # group.loc[reverse_index,'process_price']=media_price
                        group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    group.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = max_next_price

                        rt_result.loc[(rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = max_next_price
                        rt_result.loc[(rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'reverse'] = 1
                        continue
                    else:
                        logger.info('异常情况={}'.format(result_t))
                elif floor_price < middle_price:
                    max_next_price = max(middle_price * 0.99, (floor_price + middle_price) / 2)
                    # group.loc[reverse_index+1,'process_price']=max_next_price
                    group.loc[(group.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index + 1, 'product_level_key']), 'process_price'] = max_next_price

                    rt_result.loc[(rt_result.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                rt_result.product_level_key == result_t.loc[
                            reverse_index + 1, 'product_level_key']), 'process_price'] = max_next_price
                    rt_result.loc[(rt_result.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                rt_result.product_level_key == result_t.loc[
                            reverse_index + 1, 'product_level_key']), 'reverse'] = 1
                    continue

                else:
                    if floor_price < ceil_price:
                        group.loc[(group.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index + 1, 'product_level_key']), 'process_price'] = min(
                            (middle_price + floor_price) / 2,
                            min((middle_price + ceil_price) / 2, ceil_price * 0.99) * 0.99)

                        group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index, 'product_level_key']), 'process_price'] = min(
                            (middle_price + ceil_price) / 2, ceil_price * 0.99)

                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index + 1, 'product_level_key']), 'process_price'] = min(
                            (middle_price + floor_price) / 2,
                            min((middle_price + ceil_price) / 2, ceil_price * 0.99) * 0.99)
                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = min(
                            (middle_price + ceil_price) / 2, ceil_price * 0.99)


                    elif reverse_index + 2 < reverse_size:
                        # result_t.loc[reverse_index + 1, 'process_price'] = max(
                        #     result_t.loc[reverse_index, 'rank_price_max'],
                        #     result_t.loc[reverse_index + 2, 'process_price'] + 2)

                        group.loc[(group.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index + 1, 'product_level_key']), 'process_price'] = max(
                            result_t.loc[reverse_index, 'rank_price_max'],
                            result_t.loc[reverse_index + 2, 'process_price'] + 2)

                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index + 1, 'product_level_key']), 'process_price'] = max(
                            result_t.loc[reverse_index, 'rank_price_max'],
                            result_t.loc[reverse_index + 2, 'process_price'] + 2)
                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index + 1, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index + 1, 'product_level_key']), 'reverse'] = 1
                        print('dsd')
                    else:
                        group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99

                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99

                        print('未捕获到价格')

                    continue
                logger.info('end!')


            else:  #位于处理数据的最后一行
                ceil_price = result_t.loc[reverse_index - 1, 'process_price']
                ceil_sale_num_0_7 = result_t.loc[reverse_index - 1, 'sale_num_0_7']
                middls_sale_num_0_7 = result_t.loc[reverse_index, 'sale_num_0_7']
                ceil_floor_price = result_t.loc[reverse_index - 1, 'rank_price_max']
                ceil_ceil_price = result_t.loc[reverse_index - 1, 'rank_price_min']
                middle_price = result_t.loc[reverse_index, 'process_price']
                floor_price = result_t.loc[reverse_index, 'rank_price_max']
                if floor_price < ceil_ceil_price:
                    # result_t.loc[reverse_index, 'process_price']=(ceil_price+floor_price)/2
                    if floor_price < ceil_price:
                        group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index, 'product_level_key']), 'process_price'] = max(
                            (middle_price + floor_price) / 2, ceil_price * 0.99)

                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = max(
                            (middle_price + floor_price) / 2, ceil_price * 0.99)

                    else:  # 针对下面价格比上面最高价格还高的情况 需要 把上面往最高调，把下面往最低调
                        if ceil_sale_num_0_7 > 0 and ceil_sale_num_0_7 > middls_sale_num_0_7:  # 针对上面有出货数量，且比较多的情况，保持上面价格不动 压低下面价格，即使保持下面整个sku价格统一下压也可接受
                            group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    group.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99

                            rt_result.loc[
                                (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                        rt_result.product_level_key == result_t.loc[
                                    reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
                            continue
                        else:
                            group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    group.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = min(
                                (middle_price + floor_price) / 2,
                                min((middle_price + ceil_ceil_price) / 2, ceil_ceil_price * 0.99) * 0.99)

                            group.loc[(group.product_sku_key == result_t.loc[reverse_index - 1, 'product_sku_key']) & (
                                    group.product_level_key == result_t.loc[
                                reverse_index - 1, 'product_level_key']), 'process_price'] = min(
                                (middle_price + ceil_ceil_price) / 2, ceil_ceil_price * 0.99)

                            rt_result.loc[
                                (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                        rt_result.product_level_key == result_t.loc[
                                    reverse_index, 'product_level_key']), 'process_price'] = min(
                                (middle_price + floor_price) / 2,
                                min((middle_price + ceil_ceil_price) / 2, ceil_ceil_price * 0.99) * 0.99)
                            rt_result.loc[
                                (rt_result.product_sku_key == result_t.loc[reverse_index - 1, 'product_sku_key']) & (
                                        rt_result.product_level_key == result_t.loc[
                                    reverse_index - 1, 'product_level_key']), 'process_price'] = min(
                                (middle_price + ceil_ceil_price) / 2, ceil_ceil_price * 0.99)

                else:
                    # result_t.loc[reverse_index, 'process_price'] = max(floor_price*1.01,ceil_price*0.99)
                    if pd.isnull(floor_price) :
                        group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99

                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
                        continue

                    if ceil_sale_num_0_7 > 0 and ceil_sale_num_0_7 > middls_sale_num_0_7:  # 针对上面有出货数量，且比较多的情况，保持上面价格不动 压低下面价格，即使保持下面整个sku价格统一下压也可接受
                        group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                group.product_level_key == result_t.loc[
                            reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99

                        rt_result.loc[
                            (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                    rt_result.product_level_key == result_t.loc[
                                reverse_index, 'product_level_key']), 'process_price'] = ceil_price * 0.99
                        continue

                    group.loc[(group.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                            group.product_level_key == result_t.loc[
                        reverse_index, 'product_level_key']), 'process_price'] = max(floor_price * 1.01,
                                                                                     ceil_price * 0.99)

                    rt_result.loc[
                        (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                                rt_result.product_level_key == result_t.loc[
                            reverse_index, 'product_level_key']), 'process_price'] = max(floor_price * 1.01,
                                                                                       ceil_price * 0.99)
                    print('ds')
                rt_result.loc[
                    (rt_result.product_sku_key == result_t.loc[reverse_index, 'product_sku_key']) & (
                            rt_result.product_level_key == result_t.loc[
                        reverse_index, 'product_level_key']), 'reverse'] += 1

            result_t.loc[reverse_index, 'reverse_cnt']+=1

        resDf = resDf.append(result_t.copy())
        iter+=1
        logger.info('totalCnt= {},product_key={},iter = {}'.format(totalCnt,product_key , iter))
    return rt_result



def query_property_process_data():
    sku_property_rank_data=mysql_prediction_processor.load_sql(QUERY_SKU_COLUMN_RANK_SQL)
    result_t = load_pickle_data(FILE_DIR+'sku2_price_level_reverse_process1_data.pkl')

    sku_rank_price = result_t.groupby(by=['product_sku_key','template_rank'])['process_price'].agg({'rank_price_max':'max','rank_price_min':'min'}).reset_index()
    sku_rank_price['row_rank']=sku_rank_price['template_rank'].groupby(sku_rank_price['product_sku_key']).rank(ascending=True, method='first')
    result_t.product_sku_key=result_t.product_sku_key.astype(np.int)
    result_t.template_rank=result_t.template_rank.astype(np.int)
    # 价格段的数据合并到数据中
    result = result_t.merge(sku_property_rank_data, how='left', on=['product_sku_key'])
    result[COLUMNS_RANK_LIST] = result[COLUMNS_RANK_LIST].fillna(0)
    result[COLUMNS_RANK_COLUMN_LIST] = result[COLUMNS_RANK_COLUMN_LIST].fillna('unknown')
    sku_rank_price['ceil_rank']= sku_rank_price['row_rank']+1
    sku_rank_price['floor_rank']= sku_rank_price['row_rank']-1
    result = result.merge(sku_rank_price[['product_sku_key','template_rank','row_rank']], how='left', on=['product_sku_key','template_rank'])

    result = result.merge(sku_rank_price[['product_sku_key','floor_rank','rank_price_max']], how='left', left_on=['product_sku_key','template_rank'],right_on=['product_sku_key','floor_rank'])
    result = result.merge(sku_rank_price[['product_sku_key','ceil_rank','rank_price_min']], how='left', left_on=['product_sku_key','template_rank'],right_on=['product_sku_key','ceil_rank'])
    result = result.drop_duplicates()

    check_conflict_file(FILE_DIR,'sku2_price_property_process_data.pkl')
    save_pickle_data(FILE_DIR+'sku2_price_property_process_data.pkl',result)
    return result

def merge_reverse_data(rt_result,result):
    rt_result[['product_sku_key', 'product_level_key']] = rt_result[['product_sku_key', 'product_level_key']].astype( np.int64)
    result[['product_sku_key', 'product_level_key']] = result[['product_sku_key', 'product_level_key']].astype(np.int64)
    rt_result.rename(columns={'process_price':'process_price_property_inver'},inplace=True)
    result = result.merge(rt_result[['product_sku_key','product_level_key','process_price_property_inver']],how='left',on=['product_sku_key','product_level_key'])
    result.loc[result.process_price_property_inver>0,'process_price'] = result.loc[result.process_price_property_inver>0,'process_price_property_inver']
    result = result.drop(columns=['process_price_property_inver'],axis=1)
    return result

def process2_property_inverse_fun():
    t1 = time.time()
    result=query_property_process_data()
    result=load_pickle_data(FILE_DIR+'sku2_price_property_process_data.pkl')
    # result = result.loc[result.product_brand_name.isin(['苹果'])]
    # result = result.loc[result.product_key.isin([26536])]
    # result = result.loc[result.level_sub.isin(['S','A','B'])]
    result[['price_0_7', 'sale_num_0_7','price_0_3', 'sale_num_0_3']]=result[['price_0_7', 'sale_num_0_7','price_0_3', 'sale_num_0_3']].fillna(0)
    result['reverse']=0
    # rt_result = result.copy()
    # result=result.loc[result.product_key.isin([17527])]
    # result=result.loc[(result.product_key==25806) & (result.level_sub=='C')]
    # result=result.loc[result.product_key.isin([32290,277639,28527,29260,36046,36246])]
    # result=result.loc[result.product_key.isin([25680])]
    # result=result.loc[result.product_brand_id.isin([52])]
    # inverse_df_begin=inverse_df_begin.loc[(inverse_df_begin.sku_concat_name=='华为 P30  亮黑色 全网通    大陆国行（苹果系统型号M开头）') & (inverse_df_begin.product_level_name=='S') ]

    #计算倒挂率-并且打印相关信息 内存信息
    inverse_df_begin=property_sku_concat_name(result,'memory_rank','memory_name')
    # inverse_df_begin=property_inverse_rate_process(result,'memory_rank','memory_name')
    rt_result = property_inverse_process_multi(inverse_df_begin,result, 'memory_rank', 'memory_name') #处理倒挂
    result = merge_reverse_data(rt_result, result)

    #处理倒挂-存储信息
    inverse_df_begin=property_sku_concat_name(result,'storage_rank','memory_capacity_name')
    rt_result=property_inverse_process_multi(inverse_df_begin,result,'storage_rank','memory_capacity_name') #处理倒挂
    result = merge_reverse_data(rt_result, result)

    # 处理倒挂-购买渠道
    inverse_df_begin=property_sku_concat_name(result,'purchase_way_rank','purchase_channel_name')
    rt_result=property_inverse_process_multi(inverse_df_begin,result,'purchase_way_rank','purchase_channel_name') #处理倒挂
    result = merge_reverse_data(rt_result, result)

    # 处理倒挂-网络制式倒挂
    inverse_df_begin=property_sku_concat_name(result,'networks_rank','network_standard_name')
    rt_result=property_inverse_process_multi(inverse_df_begin,result,'networks_rank','network_standard_name') #处理倒挂
    result = merge_reverse_data(rt_result, result)

    # 处理倒挂-保修时长
    inverse_df_begin=property_sku_concat_name(result,'guarantee_rank','warranty_duration_name')
    rt_result=property_inverse_process_multi(inverse_df_begin,result,'guarantee_rank','warranty_duration_name') #处理倒挂
    result = merge_reverse_data(rt_result, result)

    # 处理倒挂-小型号
    inverse_df_begin=property_sku_concat_name(result,'small_model_rank','small_model_name')
    rt_result=property_inverse_process_multi(inverse_df_begin,result,'small_model_rank','small_model_name') #处理倒挂
    result = merge_reverse_data(rt_result, result)

    # save_pickle_data(FILE_DIR+'sku2_price_property_reverse_calculation_process2_df1.pkl', reverse_df)

    #
    result.drop_duplicates(subset=['product_sku_key', 'product_level_key'], keep='first', inplace=True)
    check_conflict_file(FILE_DIR,'sku2_price_property_reverse_calculation_process2_df.pkl')
    save_pickle_data(FILE_DIR+'sku2_price_property_reverse_calculation_process2_df.pkl', result)
    # result = load_pickle_data('/data/sunpengwei/tmp/sku2_price_property_reverse_calculation_process2_df.pkl')

    result_inverse_rate_df=inverse_rate_fun(result)
    df_brand_reverse_df = result_inverse_rate_df.groupby(by=['product_brand_name'])[
        ['iter_size_bi', 'iter_size', 'iverse_bi', 'iverse']].sum().reset_index()  # 这里
    df_brand_reverse_df['iverse_bi_rate'] = df_brand_reverse_df['iverse_bi'] / df_brand_reverse_df['iter_size_bi']
    df_brand_reverse_df['iverse_rate'] = df_brand_reverse_df['iverse'] / df_brand_reverse_df['iter_size']

    logger.info('process process2_property_inverse_fun use time @{}'.format((time.time() - t1)/60))
    # df_brand_reverse_df.to_csv(FILE_DIR+'sku2_df_product_calculation.csv', encoding='utf-8-sig')

    return result


if __name__=='__main__':
    process2_property_inverse_fun()
    logger.info('end!')


